梦入琼楼寒有月，行过石树冻无烟

Node.js MySql at Sequelize

2022-04-21

这是通过了 npm 注册中心所发布的 node.js 模块，并由 node.js 驱动，以 JavaScript 编写且并不需要编译，并 100% 遵循了 MIT 许可证，可直接使用 npm 进行安装：

npm install mysql

在 MySQL 创建一个表和库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
> create databases node
> use node
> create table user (
    -> id int COMMENT "编号",
    -> name varchar(40) COMMENT "名称"
    -> );
Query OK, 0 rows affected (0.015 sec)

> INSERT INTO user(id,name) value ('2','mysql_user');
Query OK, 1 row affected (0.003 sec)

> INSERT INTO user(id,name) value ('1','users_node');
Query OK, 1 row affected (0.003 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
const mysql = require('mysql')

var connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'toor',
    database: 'node'
})

connection.connect(function (err) {
    if (err) {
        console.error('connection is error' + err.stack)
        return
    }
    console.log('threadId:' + connection.threadId)
})

var showUser = "SELECT * FROM user;"

connection.query(showUser,function (err, result) {
    if (err) throw err;
    console.log('show user:',result[0])
})

connection.end() // or connection.destory()

Sequelize

在 node.js 所提供的 mysql 库中，多基于命令形式并通过 connection,query 来实现与数据库的交互，并提供了很多链接方法，如连接池等，而对于开发项目来说这种方式和并不利于项目在生产环境的发布。

因此基于 mysql 所提供的接口，Node 提供了 ORM(Object-Relational Mapping) 来将数据库的表结构来映射到数据库中，这种方式不仅统一了风格还使得与 SQL 的操作变得规范，但核心还是基于 mysql 库。

Sequelize 是一个基于 Promise 的 Node.js ORM，并适用与 Postgres、MySQL、MariaDB、SQLite、Microsoft SQL Server。相对于直接使用原生 mysql 库，直接使用 ORM 格式显得更加的规范和简洁。在 Node.js 中使用 Sequelize 可以直接通过 npm 进行安装，同样的由于 Sequelize 是一个方法约定，因此需要安装 mysql2 作为主要依赖：

npm install sequelize
npm install mysql2

首先需要创建一个 config.js 文件用于作为配置文件并通过 app.js 进行连接。

config.js

1
2
3
4
5
6
7
8
9
10

var config = {
    databases: 'node',
    username: 'root',
    password: 'toor',
    host: 'localhost',
    port: '3306'
}

module.exports = config

app.js

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

Data Types

Sequelize 的数据类型主要分为 8 种，均代替原生数据库语法以进行统一并适配多种数据库写法，其中 mysql 的对应关系为：

Id Name MySql Type Info
1 DataTypes.STRING VARCHAR(255) Strings 长度在 1 到 ~ 字符之间的可变长度的字符串（VARCHAR 字段在创建时必须定义长度，Sequelize 默认的 VARCHAR 为 255）
DataTypes.STRING(1234) VARCHAR(1234)
DataTypes.SIRING.BINARY VARCHAR BINARY BLOB 是“二进制大对象”，用于存储大量二进制数据，例如图像或其他类型的文件。
2 DataTypes.TEXT TEXT 最大长度为 65535 个字符的字段。BLOB 是“二进制大对象”，用于存储大量二进制数据，例如图像或其他类型的文件， 定义为 TEXT 的字段也包含大量数据。 两者的区别在于对存储数据的排序和比较在 BLOB 中区分大小写，而在 TEXT 字段中不区分大小写。
DataTypes.TEXT(‘tiny’) TINYTEXT 最大长度为 255 个字符的 BLOB 或 TEXT 列
4 DataTypes.Boolean TINYINT(1) Boolean 一个可以有符号或无符号的非常小的整数,如果有符号，则允许的范围是从 -128 到 127。如果是无符号，则允许的范围是从 0 到 255。
5 DataTypes.INTEGER INTEGER Numbers 一个可以有符号或无符号的非常小的整数,如果有符号，则允许的范围是从 -128 到 127。如果是无符号，则允许的范围是从 0 到 255。
DataTypes.INTEGER.UNSIGNED Unsigned & Zerofill integers(无符号和零填充整数) 一个可以有符号或无符号的非常小的整数
DataTypes.ZEROFILL 零填充
DataTypes.INTEGER.UNSIGNED.ZEROFILL 一个可以有符号或无符号的非常小的整数,且零填充
6 DataTypes.BIGINT BIGINT 可以有符号或无符号的大整数。,如果有符号，则允许范围为 -9223372036854775808 到 9223372036854775807。如果没有符号，则允许范围为 0 到 18446744073709551615。
7 DataType.FLOAT FLOAT 不能无符号的浮点数,可以定义显示长度 (M) 和小数位数 (D),这不是必需的，默认为 10,2，其中 2 是小数位数，10 是总位数（包括小数）, 对于 FLOAT，十进制精度可以达到 24 位。
8 DataType.DOUBLE DOUBLE 不能无符号的双精度浮点数,您可以定义显示长度 (M) 和小数位数 (D),这不是必需的，默认为 16,4，其中 4 是小数位数。 DOUBLE 的十进制精度可以达到 53 位, REAL 是 DOUBLE 的同义词。
9 DataType.DECIMAL DECIMAL 无法无符号的解压缩浮点数,在未压缩的十进制中，每个十进制对应一个字节。 需要定义显示长度 (M) 和小数位数 (D)。 NUMERIC 是 DECIMAL 的同义词。
10 DataType.DATE DATETIME Dates 日期和时间的组合。 格式：YYYY-MM-DD hh:mm:ss。 支持的范围是从“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。 在列定义中添加 DEFAULT 和 ON UPDATE 以获取自动初始化并更新到当前日期和时间
11 DataType.DATEONLY DATE 一个日期,格式:YYYY-MM-DD。支持的范围从“1000-01-01”到“99999 -12-31”

sequelize 可通过 sequlize.define 来定义一个模型，之后通过 model.create 来创建模型的数据信息，之后使用 sequlize.sync 同步到数据库中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

// 定义一个模型
var users = sequelize.define('users', {
    id: {
        type: Sequelize.STRING(40),
        primaryKey: true,        // 作为主键的字段一部分不可以包含 null value 且一个表中只能有一个
        uniqueKey: true         //  唯一键，该表没有两个不同的行或这些列的值相同的记录
    },
    name: Sequelize.STRING(90),
    gender: Sequelize.BOOLEAN,
    createAt: Sequelize.BIGINT,
    updateAt: Sequelize.BIGINT
}, {
    timestamps: false
})

// 当前时间戳的默认值
var now = Date.now()

users.create({
    id: '1',
    name: 'Administrator',
    gender: false,
    createAt: now,
    updateAt: now
}).then(function (p) {
    console.log('created:' + JSON.stringify(p))
}).catch(function (err) {
    console.log('failed:' + err)
})

// 同步链接
sequelize.sync()

CURD

Select

Sequelize 提供了两种查询方法，一种分别为 sequlize.fn 以及 WHERE 子句以及更为负责的 Operator，本文我们以最为常用的 WHERE 子句作为演示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

// 定义一个模型
var users = sequelize.define('users', {
    id: {
        type: Sequelize.STRING(40),
        primaryKey: true,        // 作为主键的字段一部分不可以包含 null value 且一个表中只能有一个
        uniqueKey: true         //  唯一键，该表没有两个不同的行或这些列的值相同的记录
    },
    name: Sequelize.STRING(90),
    gender: Sequelize.BOOLEAN,
    createAt: Sequelize.BIGINT,
    updateAt: Sequelize.BIGINT
}, {
    timestamps: false
})

/*
    find 1 tousers
    {"id":"1","name":"Administrator","gender":false,"createAt":1631729482972,"updateAt":1631729482972}
 */
tofind =  async () => {
    var tousers = await users.findAll({
        where: {
            id: 1
        }
    })
    console.log(`find ${tousers.length} tousers`)
    for (let u of tousers) {
        console.log(JSON.stringify(u))
    }
}

tofind()

Add

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

// 定义一个模型
var users = sequelize.define('users', {
    id: {
        type: Sequelize.STRING(40),
        primaryKey: true,        // 作为主键的字段一部分不可以包含 null value 且一个表中只能有一个
        uniqueKey: true         //  唯一键，该表没有两个不同的行或这些列的值相同的记录
    },
    name: Sequelize.STRING(90),
    gender: Sequelize.BOOLEAN,
    createAt: Sequelize.BIGINT,
    updateAt: Sequelize.BIGINT
}, {
    timestamps: false
})

var now = Date.now()

// create:{"id":2,"name":"User","gender":true,"createAt":1631731389401,"updateAt":1631731389401}
tousers = async () => {
    var addusers = await users.create({
        id: 2,
        name: 'User',
        gender: true,
        createAt: now,
        updateAt: now
    })
    console.log('create:' + JSON.stringify(addusers))
}

tousers()

Delete

通过 Sequelize 所提供的方法直接使用 destory() 对查询到的数据进行破坏即可完成删除需求：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

// 定义一个模型
var users = sequelize.define('users', {
    id: {
        type: Sequelize.STRING(40),
        primaryKey: true,        // 作为主键的字段一部分不可以包含 null value 且一个表中只能有一个
        uniqueKey: true         //  唯一键，该表没有两个不同的行或这些列的值相同的记录
    },
    name: Sequelize.STRING(90),
    gender: Sequelize.BOOLEAN,
    createAt: Sequelize.BIGINT,
    updateAt: Sequelize.BIGINT
}, {
    timestamps: false
})

todelete = async () => {
    var delUser = await users.findAll({
        where: {
            id: 2
        }
    })
    for (let d of delUser) {
        await d.destroy()
    }
}

todelete()

Updete

对于 Sequlize 的修改同样是先进行查询后使用其 Model 进行修改数据，并通过 .save() 进行保存，从而达到更改数据字段的效果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
const Sequelize = require('sequelize')
const mysql = require('mysql2')

const config = require('./config')

var sequelize = new Sequelize(config.databases, config.username, config.password, {
    host: config.host,
    dialect: 'mysql',
    pool: {
        max: 5,
        min: 0,
        idle: 30000     // 连接在被释放之前可以空闲的最长时间（以毫秒为单位）。
    }
})

sequelize
    .authenticate()
.then(() => {
    // Connection has been established successfully
    console.log('连接已成功建立')
})
.catch(err => {
    // Unable to connect to the database
    console.error('无法连接到数据库', err)
})

// 定义一个模型
var users = sequelize.define('users', {
    id: {
        type: Sequelize.STRING(40),
        primaryKey: true,        // 作为主键的字段一部分不可以包含 null value 且一个表中只能有一个
        uniqueKey: true         //  唯一键，该表没有两个不同的行或这些列的值相同的记录
    },
    name: Sequelize.STRING(90),
    gender: Sequelize.BOOLEAN,
    createAt: Sequelize.BIGINT,
    updateAt: Sequelize.BIGINT
}, {
    timestamps: false
})

var now = Date.now()

/*
    MariaDB [node]> select * from users;
    +----+---------------+--------+---------------+---------------+
    | id | name          | gender | createAt      | updateAt      |
    +----+---------------+--------+---------------+---------------+
    | 1  | Administrator |      1 | 1631729482972 | 1631731823059 |
    +----+---------------+--------+---------------+---------------+
 */
toupdete = async() => {
    var upAdmin = await users.findAll({
        where: {
            id: 1
        }
    })
    for (let u of upAdmin) {
        u.gender = true
        u.updateAt = now
        await u.save()
    }
}

toupdete()
⬅️ Go back